use ATE
go

--
IF OBJECT_ID ( 'TestItemModifyLog_Insert', 'P' ) IS NOT NULL 
    DROP PROCEDURE TestItemModifyLog_Insert;
GO
CREATE PROCEDURE TestItemModifyLog_Insert
	@TestItemID uniqueidentifier,
    @PreTestItemName nvarchar(50),
	@ProductSN nvarchar(50),
	@TestTime datetime,
	@PreTestTypeID int,
	@PreStartF decimal(18,4),
	@PreStopF decimal(18,4),
	@PreMax decimal(18,4),
	@PreMin decimal(18,4),
	@PreHighSpec decimal(18,4),
	@PreLowSpec decimal(18,4),
	@PreIsPass nvarchar(10),
	@PostTestItemName nvarchar(50),
	@PostTestTypeID int,
	@PostStartF decimal(18,4),
	@PostStopF decimal(18,4),
	@PostMax decimal(18,4),
	@PostMin decimal(18,4),
	@PostHighSpec decimal(18,4),
	@PostLowSpec decimal(18,4),
	@PostIsPass nvarchar(10),
	@ModifyTime datetime 
AS    
    --SET NOCOUNT ON;
	set @ModifyTime=(select getutcdate());
    insert into TestItemModifyLog(TestItemID,PreTestItemName,ProductSN,TestTime,PreTestTypeID,PreStartF,PreStopF,PreMax,PreMin,PreHighSpec,PreLowSpec,PreIsPass,PostTestItemName,PostTestTypeID,PostStartF,PostStopF,PostMax,PostMin,PostHighSpec,PostLowSpec,PostIsPass,ModifyTime)
	Values(@TestItemID,@PreTestItemName,@ProductSN,@TestTime,@PreTestTypeID,@PreStartF,@PreStopF,@PreMax,@PreMin,@PreHighSpec,@PreLowSpec,@PreIsPass,@PostTestItemName,@PostTestTypeID,@PostStartF,@PostStopF,@PostMax,@PostMin,@PostHighSpec,@PostLowSpec,@PostIsPass,@ModifyTime);	
GO


--
IF OBJECT_ID ( 'TestItemModifyLog_select', 'P' ) IS NOT NULL 
    DROP PROCEDURE TestItemModifyLog_select;
GO
CREATE PROCEDURE TestItemModifyLog_select
AS    
    SET NOCOUNT ON;
    select TestItemID,PreTestItemName,ProductSN,TestTime,PreTestTypeID,PreStartF,PreStopF,PreMax,PreMin,PreHighSpec,PreLowSpec,PreIsPass,PostTestItemName,PostTestTypeID,PostStartF,PostStopF,PostMax,PostMin,PostHighSpec,PostLowSpec,PostIsPass,ModifyTime
	from TestItemModifyLog 
	order by ModifyTime desc;
GO

--
IF OBJECT_ID ( 'TestItemModifyLog_selectbyID', 'P' ) IS NOT NULL 
    DROP PROCEDURE TestItemModifyLog_selectbyID;
GO
CREATE PROCEDURE TestItemModifyLog_selectbyID
	@TestItemID uniqueidentifier
AS    
    SET NOCOUNT ON;
    select TestItemID,PreTestItemName,ProductSN,TestTime,PreTestTypeID,PreStartF,PreStopF,PreMax,PreMin,PreHighSpec,PreLowSpec,PreIsPass,PostTestItemName,PostTestTypeID,PostStartF,PostStopF,PostMax,PostMin,PostHighSpec,PostLowSpec,PostIsPass,ModifyTime
	from TestItemModifyLog
	where TestItemID=@TestItemID;
GO



--
IF OBJECT_ID ( 'TestItemModifyLog_selectbySNDT', 'P' ) IS NOT NULL 
    DROP PROCEDURE TestItemModifyLog_selectbySNDT;
GO
CREATE PROCEDURE TestItemModifyLog_selectbySNDT
	@ProductSN nvarchar(50),
	@TestTime datetime
AS    
    SET NOCOUNT ON;
    select TestItemID,PreTestItemName,ProductSN,TestTime,PreTestTypeID,PreStartF,PreStopF,PreMax,PreMin,PreHighSpec,PreLowSpec,PreIsPass,PostTestItemName,PostTestTypeID,PostStartF,PostStopF,PostMax,PostMin,PostHighSpec,PostLowSpec,PostIsPass,ModifyTime
	from TestItemModifyLog
	where ProductSN=@ProductSN and TestTime=@TestTime;
GO



--
IF OBJECT_ID ('TestItemModifyLog_TRUpdate','TR') IS NOT NULL
   DROP TRIGGER TestItemModifyLog_TRUpdate
GO
CREATE TRIGGER TestItemModifyLog_TRUpdate ON TestItem
AFTER update
AS
insert into TestItemModifyLog(TestItemID,PreTestItemName,ProductSN,TestTime,PreTestTypeID,PreStartF,PreStopF,PreMax,PreMin
			,PreHighSpec,PreLowSpec,PreIsPass,PostTestItemName,PostTestTypeID,PostStartF,PostStopF,PostMax,PostMin,PostHighSpec
			,PostLowSpec,PostIsPass,ModifyTime)
			select d.TestItemID,d.TestItemName,d.ProductSN,d.TestTime,d.TestTypeID,d.StartF,d.StopF,d.Max,d.Min,d.HighSpec,d.LowSpec
			,d.IsPass,i.TestItemName,i.TestTypeID,i.StartF,i.StopF,i.Max,i.Min,i.HighSpec,i.LowSpec,i.IsPass,getdate() 
			from deleted d inner join inserted i on d.TestItemID=i.TestItemID
GO